﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_QueryJPushMessageLogV3]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_QueryJPushMessageLogV3]
GO

CREATE PROCEDURE [dbo].[sp_QueryJPushMessageLogV3](
    @MessageId INT,
    @ReceiverValue NVARCHAR(MAX),
    @Content NVARCHAR(MAX),
    @Platform INT,
    @FromUtc DATETIME,
    @ToUtc DATETIME,
    @IsTest BIT,
    @OrderDesc BIT
)
AS

BEGIN
    DECLARE @SqlStatement AS NVARCHAR(MAX);
    DECLARE @WhereStatement AS NVARCHAR(MAX) = '';
    DECLARE @CrossStatement AS NVARCHAR(MAX) = ' CROSS APPLY ML.[Audience].nodes(''/Dictionary/Item/Value/ArrayOfString/string'') AS x(reveriverValus)';

    SET @SqlStatement = 'SELECT [Key]
      ,[MessageId]
      ,[Audience]
      ,[Platform]
      ,[AppMessage]
      ,[Notification]
      ,[ResponseCode]
      ,[ResponseMessage]
      ,[AndroidDeliveredCount]
      ,[ApplePushNotificationDeliveredCount]
      ,[WindowsPhonePushNotificationDeliveredCount]
      ,[IsTest]
      ,[CreatedStamp]
      ,[LastUpdatedStamp]
  FROM [dbo].[JPushMessageLogV3] AS ML
  ';
    

    SET @MessageId = [dbo].[fn_ReformatStatement](@MessageId);

    IF @MessageId IS NOT NULL
    BEGIN
        SET @WhereStatement = '[MessageId] = ' + CONVERT(NVARCHAR(MAX), @MessageId) + ' AND ';
    END
    ELSE
    BEGIN
        IF @ReceiverValue IS NOT NULL
        BEGIN
            SET @SqlStatement = @SqlStatement + @CrossStatement;
            SET @WhereStatement = @WhereStatement + 'x.reveriverValus.value(''(.)'',''nvarchar(max)'') = ''' + @ReceiverValue + ''' AND '
        END

        SET @WhereStatement = @WhereStatement + dbo.[fn_GenerateWherePattern]('Content','LIKE',@Content,1);
        SET @WhereStatement = @WhereStatement + dbo.[fn_GenerateWherePattern]('CreatedStamp','>=',CONVERT(NVARCHAR(MAX), @FromUtc, 121),1);
        SET @WhereStatement = @WhereStatement + dbo.[fn_GenerateWherePattern]('CreatedStamp','<',CONVERT(NVARCHAR(MAX), @ToUtc, 121),1);

        IF @Platform IS NOT NULL
            SET @WhereStatement = @WhereStatement + '([Platform] & ' + CONVERT(NVARCHAR(MAX), @Platform) + ') =  ' + CONVERT(NVARCHAR(MAX), @Platform) + ' AND ';

        SET @WhereStatement = @WhereStatement + dbo.[fn_GenerateWherePattern]('IsTest','=',CONVERT(NVARCHAR(MAX),@IsTest),0);
    END

    IF(@WhereStatement <> '')
    BEGIN
        SET @WhereStatement = SUBSTRING(@WhereStatement, 0, LEN(@WhereStatement) - 3);
        SET @SqlStatement = @SqlStatement + ' WHERE ' + @WhereStatement;
        SET @SqlStatement = @SqlStatement + ' ORDER BY [MessageId] ';

        IF @OrderDesc IS NOT NULL AND @OrderDesc = 1
            SET @SqlStatement = @SqlStatement + 'DESC';
    END

    EXECUTE sp_executesql @SqlStatement;

END
GO